from DBUtils.PooledDB import PooledDB
import pymysql
import time
import base64

class DbPoolUtil(object):
    """db.config是 10.8.147.18/ywwxoltp/yunwei_auto"""
    def __init__(self, properties_config):
        self.host = properties_config.get('db_host')
        self.port = int(properties_config.get('db_port'))
        self.db_name = properties_config.get('db_name')
        self.db_username = properties_config.get('db_username')
        self.db_password = base64.b64decode(properties_config.get('db_password'))
        print('--------------------------init db pool-----------------------', self.host, self.db_name)
        print(self.db_password)
        # config = {
        #     'host': '192.168.35.143',
        #     'port': 3306,
        #     'database': 'clockin',
        #     'user': 'ffcs',
        #     'password': 'ffcs@2020',
        #     'charset': 'utf8'
        # }
        config = {
            'host': self.host,
            'port': self.port,
            'database': self.db_name,
            'user': self.db_username,
            'password': self.db_password,
            'charset': 'utf8'
        }
        #mincached：池中的初始空闲连接数
        #maxcached：池中的最大空闲连接数
        #maxshared：允许的最大共享连接数
        #maxconnections：通常允许的最大连接数
        #maxusage：单个连接的最大重用次数
        self.__pool = poolDB = PooledDB(
            # 指定数据库连接驱动
            creator=pymysql,
            # 连接池允许的最大连接数,0和None表示没有限制
            maxconnections=3,
            # 初始化时,连接池至少创建的空闲连接,0表示不创建
            mincached=2,
            # 连接池中空闲的最多连接数,0和None表示没有限制
            maxcached=5,
            # 连接池中最多共享的连接数量,0和None表示全部共享(其实没什么卵用)
            maxshared=3,
            # 连接池中如果没有可用共享连接后,是否阻塞等待,True表示等等,
            # False表示不等待然后报错
            blocking=True,
            # 开始会话前执行的命令列表
            setsession=[],
            # ping Mysql服务器检查服务是否可用
            ping=0,
            **config
        )

    """查询语句"""
    def execute_query(self, sql, args=()):
        result = ()
        conn = self.__pool.connection()
        cur = conn.cursor()
        try:
            cur.execute(sql, args)
            result = cur.fetchall()
            # TODO
            # time.sleep(1)
        except Exception as e:
            print('db select error:' + str(e))
        cur.close()
        conn.close()
        return result

    def execute_iud(self, sql, args=()):
        """
        执行增删改语句
        """
        conn = self.__pool.connection()
        cur = conn.cursor()
        count = 0
        try:
            count = cur.execute(sql, args)
            conn.commit()
        except Exception as e:
            print('db exec error:' + str(e))
            conn.rollback()
            count = -1
        cur.close()
        conn.close()
        return count

    def execute_many_iud(self, sql, args):
        """
        批量执行增删改语句
        :param args:参数,内部元组或列表大小与sql语句中参数数量一致
        """
        conn = self.__pool.connection()
        cur = conn.cursor()
        count = 0
        try:
            count = cur.executemany(sql, args)
            conn.commit()
        except Exception as e:
            print('db exec many error:' + str(e))
            conn.rollback()
        cur.close()
        conn.close()
        return count

if __name__ == '__main__':
    dbpool_util = DbPoolUtil()
    print(dbpool_util)